package com.meego.common.io;

import com.meego.common.log.GA;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Map.Entry;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Excel {

	public static final WritableFont times8ptBold = new WritableFont(WritableFont.TIMES, 8, WritableFont.BOLD, false);
	public static final WritableFont times8ptNoBold = new WritableFont(WritableFont.TIMES, 8, WritableFont.NO_BOLD, false);
	public static final WritableFont times8ptItalicBold = new WritableFont(WritableFont.TIMES, 8, WritableFont.BOLD, false);
	public static final WritableFont times8ptItalicNoBold = new WritableFont(WritableFont.TIMES, 8, WritableFont.NO_BOLD, false);
	
	public static final WritableFont times10ptBold = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false);
	public static final WritableFont times10ptNoBold = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false);
	public static final WritableFont times10ptItalicBold = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, true);
	public static final WritableFont times10ptItalicNoBold = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, true);

	public static final WritableFont times13ptBold = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD, false);
	public static final WritableFont times13ptNoBold = new WritableFont(WritableFont.TIMES, 13, WritableFont.NO_BOLD, false);
	public static final WritableFont times13ptItalicBold = new WritableFont(WritableFont.TIMES, 13, WritableFont.BOLD, true);
	public static final WritableFont times13ptItalicNoBold = new WritableFont(WritableFont.TIMES, 13, WritableFont.NO_BOLD, true);

	public static final WritableFont times16ptBold = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false);
	public static final WritableFont times16ptNoBold = new WritableFont(WritableFont.TIMES, 16, WritableFont.NO_BOLD, false);
	public static final WritableFont times16ptItalicBold = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, true);
	public static final WritableFont times16ptItalicNoBold = new WritableFont(WritableFont.TIMES, 16, WritableFont.NO_BOLD, true);
	
	public static final WritableFont times18ptBold = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, false);
	public static final WritableFont times18ptNoBold = new WritableFont(WritableFont.TIMES, 18, WritableFont.NO_BOLD, false);
	public static final WritableFont times18ptItalicBold = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
	public static final WritableFont times18ptItalicNoBold = new WritableFont(WritableFont.TIMES, 18, WritableFont.NO_BOLD, true);
	
	private WritableCellFormat defaultFont = new WritableCellFormat(times13ptNoBold);
	
	private static Excel instance;
	
	public static Excel getInstance(){
		return instance == null ? new Excel() : instance;
	}
	private Excel(){
		
	}
	
	private void addCaption(WritableSheet sheet, int column, int row, Object s, WritableCellFormat font) throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, String.valueOf(s), (font == null) ? defaultFont : font);
		sheet.addCell(label);
	}
	
	private void createHeader(WritableSheet sheet, String title, String subTitle, String companyName, String address, int cellMerge) throws WriteException {
		// Lets automatically wrap the cells
		WritableCellFormat defaultFont = new WritableCellFormat(times13ptNoBold);
		int row = 0;
		if(companyName != null && !companyName.isEmpty()){
			sheet.mergeCells(0, row, cellMerge, row);
			addCaption(sheet, 0, row, companyName, defaultFont);
			row++;
		}
		if(address != null && !address.isEmpty()){
			defaultFont = new WritableCellFormat(times13ptItalicNoBold);
			sheet.mergeCells(0, row, cellMerge, row);
			addCaption(sheet, 0, row, address, defaultFont);
			row++;
		}
		if(title != null && !title.isEmpty()){
			defaultFont = new WritableCellFormat(times16ptBold);
			defaultFont.setAlignment(Alignment.CENTRE);
			defaultFont.setVerticalAlignment(VerticalAlignment.CENTRE);
			sheet.setRowView(row, 500);
			sheet.mergeCells(0, row, cellMerge, row);
			addCaption(sheet, 0, row, title, defaultFont);
			row++;
		}
		if(subTitle != null && !subTitle.isEmpty()){
			defaultFont = new WritableCellFormat(times13ptNoBold);
			defaultFont.setAlignment(Alignment.CENTRE);
			defaultFont.setVerticalAlignment(VerticalAlignment.CENTRE);
			sheet.setRowView(row, 400);
			sheet.mergeCells(0, row, cellMerge, row);
			addCaption(sheet, 0, row, subTitle, defaultFont);
			row++;
		}
	}
	public void test(OutputStream out, Map<String, String> map) {
		WritableWorkbook workbook = null;
  		WorkbookSettings wbSettings = new WorkbookSettings();
  		wbSettings.setLocale(new Locale("en", "EN"));
		try {
			workbook = Workbook.createWorkbook(out, wbSettings);
		} catch (IOException e) { e.printStackTrace();}
		workbook.createSheet("mpin", 0);
		WritableSheet excelSheet = workbook.getSheet(0);
		int row = 0;
		try{
			//Create Header
//			createHeader(excelSheet, title, subTitle, companyName, address, 6);
			// Insert first row --> insert row title here
			excelSheet.setColumnView(0, 40);
			excelSheet.setColumnView(1, 100);
			
			addCaption(excelSheet, 0, row, "reference", null);
			addCaption(excelSheet, 1, row, "phash", null);
			defaultFont = new WritableCellFormat(times13ptNoBold);
			//Insert content
			Iterator<Entry<String, String>> it = map.entrySet().iterator();
			while (it.hasNext()) {
				row++;
		        Map.Entry<String, String> pairs = (Map.Entry<String, String>)it.next();
		        System.out.println("key: " +pairs.getKey() + " value: " + pairs.getValue());

				addCaption(excelSheet, 0, row, pairs.getKey(), null);
				addCaption(excelSheet, 1, row, pairs.getValue(), null);
		    }

			workbook.write();
			workbook.close();
		}
		catch (Exception e) {
			GA.billing.error("", e);
		}
	}
	private void insertFirstRow(WritableSheet sheet, int row){
		try {
			defaultFont = new WritableCellFormat(times13ptBold);
			defaultFont.setAlignment(Alignment.CENTRE);
			defaultFont.setVerticalAlignment(VerticalAlignment.CENTRE);
			//mergeCell(int col1, int row1, int col2, int row2)
			sheet.setColumnView(0, 8);
			sheet.setColumnView(1, 30);
			
			addCaption(sheet, 0, row, "No", null);
			addCaption(sheet, 1, row, "content", null);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public List<String> excel(String inputFile) throws IOException  {
		List<String> list = new ArrayList<String>();
		java.io.File inputWorkbook = new java.io.File(inputFile);
		Workbook w;
		try {
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			Sheet sheet = w.getSheet(0);
			String data;
			int checkValid;
			for (int j = 0; j < sheet.getRows(); j++) {
				Cell cell = sheet.getCell(0, j);
				list.add(cell.getContents());
			}
		} catch (BiffException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public static void main(String[] args) {
//		FileOutputStream out = null;
//		try {
//			out = new FileOutputStream("D:\\tetsexcel.xls");
//		} catch (FileNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		ArrayList<String> list = new ArrayList<String>();
//		for(int i = 0; i<100; i++)
//			list.add("row content: " + i);
//		getInstance().test(out, "test", list, "Test create excel file", "simple excel file", "JAVAVS", "416 Nguyễn Văn Công");
		try {
			System.out.println(getInstance().excel("C:\\Users\\TueHM.D1-TUEHM\\Desktop\\User wrong phash-831.xls"));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}